﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Text.RegularExpressions;
namespace DBManager
{
    /// <summary>
    /// This clsss contains methods for extend SQL commands.
    /// </summary>
    public  class SQLHelper
    {
        public static string CheckNull(string strDBName, string sql)
        {
            if (DBReader.IsOracleDatabase(strDBName) == true)
            {
                string tmpSQL = sql.ToUpper();
                int pos = tmpSQL.IndexOf("WHERE");
                if (pos > 0)
                {
                    string whereClause = sql.Substring(pos);
                    whereClause = Regex.Replace(whereClause, @"<[\s]*>[\s]*\'\'", " is not null ", RegexOptions.IgnoreCase);
                    whereClause = Regex.Replace(whereClause, @"![\s]*=[\s]*\'\'", " is not null ", RegexOptions.IgnoreCase);
                    whereClause = Regex.Replace(whereClause, @"=[\s]*\'\'", " is null ", RegexOptions.IgnoreCase);
                    sql = sql.Substring(0, pos) + whereClause;
                }
                return sql;
            }
            else
            {
                return sql;
            }
        }


        public static string Extend(string strDBName, string sql)
        {
            return CheckNull(strDBName,sql);
        }

        public static string Extend(string strDBName, string sql, object p1)
        {
            return CheckNull(strDBName, sql.Replace("%1", p1.ToString()));
        }
        public static string Extend(string strDBName, string sql, object p1, object p2)
        {
            return CheckNull(strDBName, sql.Replace("%2", p2.ToString()).Replace("%1", p1.ToString()));
        }
        public static string Extend(string strDBName, string sql, object p1, object p2, object p3)
        {
            return CheckNull(strDBName, sql.Replace("%3", p3.ToString()).Replace("%2", p2.ToString()).Replace("%1", p1.ToString()));
        }
        public static string Extend(string strDBName, string sql, object p1, object p2, object p3, object p4)
        {
            return CheckNull(strDBName, sql.Replace("%4", p4.ToString()).Replace("%3", p3.ToString()).Replace("%2", p2.ToString()).Replace("%1", p1.ToString()));
        }
        public static string Extend(string strDBName, string sql, object p1, object p2, object p3, object p4, object p5)
        {
            return CheckNull(strDBName, sql.Replace("%5", p5.ToString()).Replace("%4", p4.ToString()).Replace("%3", p3.ToString()).Replace("%2", p2.ToString()).Replace("%1", p1.ToString()));
        }
        public static string Extend(string strDBName, string sql, object p1, object p2, object p3, object p4, object p5, object p6)
        {
            return CheckNull(strDBName, sql.Replace("%6", p6.ToString()).Replace("%5", p5.ToString()).Replace("%4", p4.ToString()).Replace("%3", p3.ToString()).Replace("%2", p2.ToString()).Replace("%1", p1.ToString()));
        }
        /// <summary>
        /// If %* is found in sql then it is replaced by px items separated by commas.
        /// E.g. %* -> 'name', 'description'
        /// </summary>
        /// <param name="sql">SQL command with %1, %2,... or %*</param>
        /// <param name="px">String items to replace</param>
        /// <returns></returns>
        public static string Extend(string strDBName, string sql, ArrayList px)
        {
            int i;
            object s;
            DateTime dtTmp;

            if (sql.IndexOf("%*") != -1)
            {
                StringBuilder LValues = new StringBuilder();

                for (i = 0; i < px.Count; i++)
                {
                    s = px[i];

                    if (s.GetType() == typeof(DateTime))
                    {
                        dtTmp = (DateTime)s;
                        LValues.Append("'");
                        LValues.Append(dtTmp.ToString("yyyy-mm-dd hh24:mi:ss"));
                        LValues.Append("', ");
                    }
                    else
                    {
                        LValues.Append("'");
                        LValues.Append(s.ToString());
                        LValues.Append("', ");
                    }
                }
                LValues = LValues.Remove(LValues.Length - 2, 2);
                sql = sql.Replace("%*", LValues.ToString());
            }
            else
            {
                string LS;

                for (i = px.Count - 1; i >= 0; i--)
                {
                    if (px[i].GetType() == typeof(DateTime))
                    {
                        dtTmp = (DateTime)px[i];
                        LS = dtTmp.ToString("yyyy-mm-dd hh24:mi:ss");
                    }
                    else
                    {
                        LS = px[i].ToString();
                    }

                    sql = sql.Replace("%" + (i + 1).ToString(), LS);
                }

            }
            return CheckNull(strDBName, sql);
        }
        public static string Extend(string strDBName, string sql, object[] px)
        {
            if (px == null)
                return Extend(strDBName, sql);
            else
                return Extend(strDBName, sql, new ArrayList(px));
        }
    }
}
